package server;

/**
 * Class that aids in testing the database.
 * @author Terry
 */
import iface.HvZUser;
import iface.Logger;
import iface.UserTypes;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

public class DBControlPanel extends Database {

    private String admin;
    private String password;

    /**
     * Constructs a new DBControlPanel
     * 
     * @param username
     *            the root username
     * @param password
     *            the root password
     */
    public DBControlPanel(String username, String password) {
        Logger.log("DBCONTROLPANEL: DBCP instance created");
        admin = username;
        this.password = password;
    }

    /**
     * @return a list of all users in the database
     */
    public List<HvZUser> getAllUsers() {
        try {
            Connection conn = establishConnection(admin, password);
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery("SELECT * FROM Users");
            List<HvZUser> ll = new LinkedList<HvZUser>();
            while (rs.next()) {

                UserTypes type = convertIntToType(rs.getInt(3));
                ll.add(new HvZUser(rs.getString(1), type, rs.getString(2)));
            }
            conn.close();
            return ll;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * Updates a user to a new type
     * 
     * @param username
     *            the user to update
     * @param type
     *            the type to update the user to. 0 = human, 1 = zombie, 2 =
     *            observer
     * @return true if successful, false otherwise
     */
    public boolean updateUserStatus(String username, int type) {
        if (type < 0 || type > 2)
            return false;

        try {
            Connection conn = establishConnection(admin, password);
            PreparedStatement ps = conn
                    .prepareStatement("UPDATE Users SET type = ? WHERE net_id = ?");
            ps.setInt(1, type);
            ps.setString(2, username);
            ps.executeUpdate();
            Logger.log("DBCP|updateStatus: user " + username + " changed to "
                    + type);
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * Deletes a user from the database
     * 
     * @param username
     *            the user to delete
     * @return true if successful, false otherwise
     * @throws SQLException
     *             if there is a connection error
     */
    public boolean deleteUser(String username) throws SQLException {
        Connection conn = establishConnection(admin, password);
        try {
            conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            conn.setAutoCommit(false);
            PreparedStatement ps = conn
                    .prepareStatement("DELETE FROM Users WHERE net_id = ?");
            ps.setString(1, username);
            ps.executeUpdate();
            Logger.log("DBCP|deleteUser: user " + username
                    + "has been deleted from " + getDatabase());
            conn.commit();
            conn.setAutoCommit(true);
            conn.close();
            return true;
        } catch (SQLException e) {
            conn.rollback();
            e.printStackTrace();
        }
        return false;
    }

    /**
     * Deletes all users from the database
     * 
     * @return true if successful, false otherwise
     * @throws SQLException
     *             if there is a connection error
     */
    public boolean deleteAll() throws SQLException {
        Connection conn = establishConnection(admin, password);
        try {
            conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            conn.setAutoCommit(false);
            PreparedStatement ps = conn.prepareStatement("DELETE FROM Users");
            ps.executeUpdate();
            Logger.log("DBCP|deleteAll: All users deleted from "
                    + getDatabase());
            conn.commit();
            conn.setAutoCommit(true);
            conn.close();
            return true;
        } catch (SQLException e) {
            conn.rollback();
            e.printStackTrace();
        }
        return false;
    }
}
